﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.VersionControl.Client;

namespace DatabaseScripter
{
    public class Program
    {
        private static readonly List<string> AddedFiles = new List<string>();
        private static readonly List<string> EditedFiles = new List<string>();
        private static readonly List<string> DeletedFiles = new List<string>();

        public static void Main(string[] args)
        {
            SqlConnection sqlConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            Server server = new Server(new ServerConnection(sqlConnection));
            Database database = server.Databases[sqlConnection.Database];

            // Script tables
            Scripter scripter = new Scripter(server);
            scripter.Options.DriAll = true;
            scripter.Options.NoCollation = true;
            scripter.Options.Indexes = true;
            scripter.Options.XmlIndexes = true;
            foreach (Table table in database.Tables.OfType<Table>().Where(t => !t.IsSystemObject))
            {
                if (!Properties.Settings.Default.TablesWithData.Contains(table.Schema + "." + table.Name))
                {
                    Script(scripter, table.Schema + "." + table.Name, table.Urn, Properties.Settings.Default.TablePath,
                        Path.Combine(Properties.Settings.Default.TablePath, table.Schema + "." + table.Name + ".Table.sql"));
                }
            }

            // Script tables with data
            scripter = new Scripter(server);
            scripter.Options.DriAll = true;
            scripter.Options.NoCollation = true;
            scripter.Options.Indexes = true;
            scripter.Options.XmlIndexes = true;
            scripter.Options.ScriptData = true;
            foreach (Table table in database.Tables.OfType<Table>().Where(t => !t.IsSystemObject))
            {
                if (Properties.Settings.Default.TablesWithData.Contains(table.Schema + "." + table.Name))
                {
                    Script(scripter, table.Schema + "." + table.Name, table.Urn, Properties.Settings.Default.TableWithDataPath,
                        Path.Combine(Properties.Settings.Default.TableWithDataPath, table.Schema + "." + table.Name + ".Table.sql"));
                }
            }

            // Remove no longer existing tables
            if (Directory.Exists(Properties.Settings.Default.TablePath))
            {
                List<string> files = Directory.GetFiles(Properties.Settings.Default.TablePath).ToList();
                files.AddRange(Directory.GetFiles(Properties.Settings.Default.TableWithDataPath));
                foreach (string filePath in files)
                {
                    if (database.Tables.OfType<Table>().Where(t => !t.IsSystemObject).All(t => t.Schema + "." + t.Name + ".Table.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script views
            scripter = new Scripter(server);
            scripter.Options.DriAll = true;
            scripter.Options.NoCollation = true;
            scripter.Options.Indexes = true;
            scripter.Options.XmlIndexes = true;
            foreach (View view in database.Views.OfType<View>().Where(t => !t.IsSystemObject))
            {
                Script(scripter, view.Schema + "." + view.Name, view.Urn, Properties.Settings.Default.ViewPath,
                    Path.Combine(Properties.Settings.Default.ViewPath, view.Schema + "." + view.Name + ".View.sql"));
            }

            // Remove no longer existing views
            if (Directory.Exists(Properties.Settings.Default.ViewPath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.ViewPath))
                {
                    if (database.Views.OfType<View>().Where(t => !t.IsSystemObject).All(t => t.Schema + "." + t.Name + ".View.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script stored procedures
            scripter = new Scripter(server);
            foreach (StoredProcedure procedure in database.StoredProcedures.OfType<StoredProcedure>().Where(t => !t.IsSystemObject))
            {
                Script(scripter, procedure.Schema + "." + procedure.Name, procedure.Urn, Properties.Settings.Default.StoredProcedurePath,
                    Path.Combine(Properties.Settings.Default.StoredProcedurePath, procedure.Schema + "." + procedure.Name + ".StoredProcedure.sql"));
            }

            // Remove no longer existing stored procedures
            if (Directory.Exists(Properties.Settings.Default.StoredProcedurePath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.StoredProcedurePath))
                {
                    if (database.StoredProcedures.OfType<StoredProcedure>().Where(t => !t.IsSystemObject).All(t => t.Schema + "." + t.Name + ".StoredProcedure.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script user defined functions
            scripter = new Scripter(server);
            foreach (UserDefinedFunction function in database.UserDefinedFunctions.OfType<UserDefinedFunction>().Where(t => !t.IsSystemObject))
            {
                Script(scripter, function.Schema + "." + function.Name, function.Urn, Properties.Settings.Default.UserDefinedFunctionPath,
                    Path.Combine(Properties.Settings.Default.UserDefinedFunctionPath, function.Schema + "." + function.Name + ".UserDefinedFunction.sql"));
            }

            // Remove no longer existing user defined functions
            if (Directory.Exists(Properties.Settings.Default.UserDefinedFunctionPath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.UserDefinedFunctionPath))
                {
                    if (database.UserDefinedFunctions.OfType<UserDefinedFunction>().Where(t => !t.IsSystemObject).All(t => t.Schema + "." + t.Name + ".UserDefinedFunction.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script user defined table types
            scripter = new Scripter(server);
            foreach (UserDefinedTableType tableType in database.UserDefinedTableTypes.OfType<UserDefinedTableType>().Where(t => t.IsUserDefined))
            {
                Script(scripter, tableType.Schema + "." + tableType.Name, tableType.Urn, Properties.Settings.Default.UserDefinedTableTypePath,
                    Path.Combine(Properties.Settings.Default.UserDefinedTableTypePath, tableType.Schema + "." + tableType.Name + ".UserDefinedTableType.sql"));
            }

            // Remove no longer existing user defined table types
            if (Directory.Exists(Properties.Settings.Default.UserDefinedTableTypePath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.UserDefinedTableTypePath))
                {
                    if (database.UserDefinedTableTypes.OfType<UserDefinedTableType>().Where(t => t.IsUserDefined).All(t => t.Schema + "." + t.Name + ".UserDefinedTableType.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script user defined aggregates
            scripter = new Scripter(server);
            foreach (UserDefinedAggregate aggregate in database.UserDefinedAggregates.OfType<UserDefinedAggregate>())
            {
                Script(scripter, aggregate.Schema + "." + aggregate.Name, aggregate.Urn, Properties.Settings.Default.UserDefinedAggregatePath,
                    Path.Combine(Properties.Settings.Default.UserDefinedAggregatePath, aggregate.Schema + "." + aggregate.Name + ".Aggregate.sql"));
            }

            // Remove no longer existing user defined aggregates
            if (Directory.Exists(Properties.Settings.Default.UserDefinedAggregatePath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.UserDefinedAggregatePath))
                {
                    if (database.UserDefinedAggregates.OfType<UserDefinedAggregate>().All(t => t.Schema + "." + t.Name + ".Aggregate.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script synonyms
            scripter = new Scripter(server);
            foreach (Synonym synonym in database.Synonyms.OfType<Synonym>())
            {
                Script(scripter, synonym.Schema + "." + synonym.Name, synonym.Urn, Properties.Settings.Default.SynonymPath,
                    Path.Combine(Properties.Settings.Default.SynonymPath, synonym.Schema + "." + synonym.Name + ".Synonym.sql"));
            }

            // Remove no longer existing synonyms
            if (Directory.Exists(Properties.Settings.Default.SynonymPath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.SynonymPath))
                {
                    if (database.Synonyms.OfType<Synonym>().All(t => t.Schema + "." + t.Name + ".Synonym.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script full text catalogs
            scripter = new Scripter(server);
            foreach (FullTextCatalog catalog in database.FullTextCatalogs.OfType<FullTextCatalog>())
            {
                Script(scripter, catalog.Name, catalog.Urn, Properties.Settings.Default.FullTextCatalogPath,
                    Path.Combine(Properties.Settings.Default.FullTextCatalogPath, catalog.Name + ".FullTextCatalog.sql"));
            }

            // Remove no longer existing full text catalogs
            if (Directory.Exists(Properties.Settings.Default.FullTextCatalogPath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.FullTextCatalogPath))
                {
                    if (database.FullTextCatalogs.OfType<FullTextCatalog>().All(t => t.Name + ".FullTextCatalog.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            // Script sql assemblies
            scripter = new Scripter(server);
            foreach (SqlAssembly assembly in database.Assemblies.OfType<SqlAssembly>().Where(t => !t.IsSystemObject))
            {
                Script(scripter, assembly.Name, assembly.Urn, Properties.Settings.Default.SqlAssemblyPath,
                    Path.Combine(Properties.Settings.Default.SqlAssemblyPath, assembly.Name + ".SqlAssembly.sql"));
            }

            // Remove no longer existing sql assemblies
            if (Directory.Exists(Properties.Settings.Default.SqlAssemblyPath))
            {
                foreach (string filePath in Directory.GetFiles(Properties.Settings.Default.SqlAssemblyPath))
                {
                    if (database.Assemblies.OfType<SqlAssembly>().All(t => t.Name + ".SqlAssembly.sql" != Path.GetFileName(filePath)))
                    {
                        MarkDeleted(filePath);
                    }
                }
            }

            string file = AddedFiles.FirstOrDefault() ?? EditedFiles.FirstOrDefault() ?? DeletedFiles.FirstOrDefault();
            if (file != null)
            {
                TfsTeamProjectCollection projectCollection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(Properties.Settings.Default.TfsServer));
                WorkspaceInfo workspaceInfo = Workstation.Current.GetLocalWorkspaceInfo(Path.Combine(Environment.CurrentDirectory, file));
                if (workspaceInfo != null)
                {
                    Workspace workspace = workspaceInfo.GetWorkspace(projectCollection);
                    if (workspace != null)
                    {
                        workspace.VersionControlServer.NonFatalError += VersionControlServer_NonFatalError;
                        workspace.CheckIn(workspace.GetPendingChanges(), Properties.Settings.Default.CheckInMessage);
                    }
                }

                projectCollection.Dispose();
            }

            sqlConnection.Close();

            foreach (string deletedFile in DeletedFiles)
            {
                File.Delete(deletedFile);
            }
        }

        private static void Script(Scripter scripter, string fullName, Urn urn, string path, string fileName)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();
            Console.Write("Scripting " + fullName + "...");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            bool editFile = File.Exists(fileName);
            if (editFile)
            {
                TfsTeamProjectCollection projectCollection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(Properties.Settings.Default.TfsServer));
                WorkspaceInfo workspaceInfo = Workstation.Current.GetLocalWorkspaceInfo(Path.Combine(Environment.CurrentDirectory, fileName));
                if (workspaceInfo != null)
                {
                    Workspace workspace = workspaceInfo.GetWorkspace(projectCollection);
                    if (workspace != null)
                    {
                        workspace.VersionControlServer.NonFatalError += VersionControlServer_NonFatalError;
                        workspace.PendEdit(fileName);
                        EditedFiles.Add(fileName);
                    }
                }

                projectCollection.Dispose();
            }

            List<string> stringCollection = scripter.EnumScript(new[] { urn }).ToList();
            FileStream stream = new FileStream(fileName, FileMode.Create);
            StreamWriter writer = new StreamWriter(stream);
            foreach (string lines in stringCollection)
            {
                writer.WriteLine(lines.Trim());
                writer.WriteLine("GO");
            }

            writer.Close();

            if (!editFile)
            {
                TfsTeamProjectCollection projectCollection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(Properties.Settings.Default.TfsServer));
                WorkspaceInfo workspaceInfo = Workstation.Current.GetLocalWorkspaceInfo(Path.Combine(Environment.CurrentDirectory, fileName));
                if (workspaceInfo != null)
                {
                    Workspace workspace = workspaceInfo.GetWorkspace(projectCollection);
                    if (workspace != null)
                    {
                        workspace.VersionControlServer.NonFatalError += VersionControlServer_NonFatalError;
                        workspace.PendAdd(fileName);
                        AddedFiles.Add(fileName);
                    }
                }

                projectCollection.Dispose();
            }

            watch.Stop();
            Console.WriteLine(watch.ElapsedMilliseconds + "ms");
        }

        private static void MarkDeleted(string filePath)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();
            Console.Write("Removing " + filePath + "...");

            TfsTeamProjectCollection projectCollection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(Properties.Settings.Default.TfsServer));
            WorkspaceInfo workspaceInfo = Workstation.Current.GetLocalWorkspaceInfo(Path.Combine(Environment.CurrentDirectory, filePath));
            if (workspaceInfo != null)
            {
                Workspace workspace = workspaceInfo.GetWorkspace(projectCollection);
                if (workspace != null)
                {
                    workspace.VersionControlServer.NonFatalError += VersionControlServer_NonFatalError;
                    DeletedFiles.Add(filePath);
                }
            }

            projectCollection.Dispose();

            watch.Stop();
            Console.WriteLine(watch.ElapsedMilliseconds + "ms");
        }

        private static void VersionControlServer_NonFatalError(object sender, ExceptionEventArgs e)
        {
            if (e.Exception != null)
            {
                Console.WriteLine(e.Exception.Message);
            }
            else if (e.Failure != null)
            {
                Console.WriteLine(e.Failure.Message);
            }
            else
            {
                Console.WriteLine(e);
            }
        }
    }
}